/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import action.administration.KPrev;

/**
 *
 * @author Thai Ha Cong
 */
public class KPDAO {

    private Connection con;

    public KPDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }

    public List<KPrev> getAllKPrev() {
        List<KPrev> result = new ArrayList<KPrev>();

        double costRatio = 0.1;

        try {
            String sqlst = "SELECT product.idProduct, product.name, product.unitPrice, receiptitem.receiptPrice "
                    + "FROM product, receiptitem, receipt "
                    + "WHERE product.idProduct = receiptitem.Product_idProduct "
                    + "     AND receipt.idReceipt = receiptitem.Receipt_idReceipt "
                    + "     AND receipt.createTime IN ( "
                    + "         SELECT MAX(c.createTime) FROM product A, receiptitem B, receipt C "
                    + "		WHERE A.idProduct = B.Product_idProduct "
                    + "             AND C.idReceipt = B.Receipt_idReceipt "
                    + "             AND A.idProduct = product.idProduct "
                    + "         ) "
                    + "GROUP BY product.idProduct "
                    + "ORDER BY product.idProduct ASC;";
            PreparedStatement ps = con.prepareStatement(sqlst);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idProduct = rs.getInt(1);
                String name = rs.getString(2);
                double unitPrice = rs.getDouble(3);
                double receiptPrice = rs.getDouble(4);
                double kpRevenue = (unitPrice - receiptPrice) * (1 - costRatio);

                KPrev temp = new KPrev(idProduct, name, unitPrice, receiptPrice, kpRevenue);

                result.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(KPDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public KPrev getKPrevFromProductId(int id) {
        KPrev result = null;

        double costRatio = 0.1;

        try {
            String sqlst = "SELECT product.idProduct, product.name, product.unitPrice, receiptitem.receiptPrice"
                    + " FROM product, receiptitem, receipt"
                    + " WHERE product.idProduct = ?"
                    + "	AND product.idProduct = receiptitem.Product_idProduct"
                    + "	AND receipt.idReceipt = receiptitem.Receipt_idReceipt"
                    + "	ORDER BY receipt.createTime DESC LIMIT 1;";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, id);

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                Integer idProduct = rs.getInt(1);
                String name = rs.getString(2);
                double unitPrice = rs.getDouble(3);
                double receiptPrice = rs.getDouble(4);
                double kpRevenue = (unitPrice - receiptPrice) * (1 - costRatio);

                result = new KPrev(idProduct, name, unitPrice, receiptPrice, kpRevenue);
            }

        } catch (SQLException ex) {
            Logger.getLogger(KPDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public static void main(String[] args) {
        KPDAO run = new KPDAO();

        for (KPrev k : run.getAllKPrev()) {
            System.out.println(k.toString());
        }
        System.out.println(run.getKPrevFromProductId(1).toString());
    }
}
